/*
 * 作者：姚俊
 * 功能：论著的逻辑处理
 * 日期：2015-05-29
 */
package admin.service.fruits;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import beanDao.MapToBean;

import teacher.bean.Teacher;
import teacher.bean.Treatise;
import tools.StringDate;

import database.dao.MysqlCrud;

public class TreatiseService {
	
	
	/**
	 * @category 根据论著的ID来查询一个论著
	 * @author yaojun
	 * @param treatise Treatise 传入一个论著信息的对象，该对象必须有论著信息的ID
	 * @return Treatise 返回的是一个论著信息的对象
	 */
	public Treatise getTreatise(Treatise treatise)
	{
		Treatise tre =new Treatise();
		String sql="select * from Treatise where treId=?";
		String[]  parameter={String.valueOf(treatise.getTreId())};
        MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql, parameter);
		if(lists.size()>0&&lists!=null)
		{	
			Map map=lists.get(0);
			tre=new MapToBean().MapToTreatise(map);
		}
		
		return tre;
	}	
	
	/**
	 * @category 根据论著信息的ID来修改这个论著的信息
	 * @author yaojun
	 * @param inn1 Treatise 传入一个要用于修改的Treatise对象
	 * @param inn2 Treatise 传入一个根据这个Treatise对象的ID来查找需要修改的哪一条论著信息数据
	 * @return boolean 成功则返回true
	 */
	public boolean updateTreatise(Treatise treatise1,Treatise treatise2)
	{
		boolean b=false;
		String sql="update Treatise set name=?,author=?," +
		"date=?,state=?,class=?,press=?,abstract=?,upload=?,bookNum=?," +
		"teaId=? where treId=?";
		
		String[] parameter={
				String.valueOf(treatise1.getName()),
				String.valueOf(treatise1.getAuthor()),
				String.valueOf(StringDate.DateToString(treatise1.getDate())),
				String.valueOf(treatise1.getState()),
				String.valueOf(treatise1.getClass_()),
				String.valueOf(treatise1.getPress()),
				String.valueOf(treatise1.getAbstract_()),
				String.valueOf(treatise1.getUpload()),
				String.valueOf(treatise1.getBookNum()),
				String.valueOf(treatise1.getTeaId()),
				String.valueOf(treatise2.getTreId())
		};

		MysqlCrud mysqlcurd=new MysqlCrud();
		b=mysqlcurd.InsertAndUpdateData(sql, parameter);
		return b;
	}
	
	
	/**
	 * @category 分页查询所有论著
	 * @param pageSize int 一个页面要显示pageSize条个数据
	 * @param pageNow int 从pageNow当前的数据来显示
	 * @return Treatise List<Treatise> 返回查询到的论著列表
	 */
	public List<Treatise> getSegregateTreatiseByAll(int pageNow,int pageSize) {
		String sql= "select * from (select * from treatise order by state,class,date desc) as T WHERE T.state>3 and T.treId LIMIT " +
				""+(pageNow-1)+" ,"+pageSize+"";
		int i=0;
		List<Treatise> treatises=new ArrayList<Treatise>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> list = mysqlcurd.SelectData(sql, null);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			treatises.add(new MapToBean().MapToTreatise(map));
			i++;
		}
		return treatises;
	}
	
	/**
	 * @category 分页查询所有论著
	 * @param pageSize int 一个页面要显示pageSize条个数据
	 * @param pageNow int 从pageNow当前的数据来显示
	 * @param name String 论著的名称
	 * @return Treatise List<Treatise> 返回查询到的论著列表
	 */
	public List<Treatise> getSegregateTreatiseByName(String name,int pageNow,int pageSize) {
		String sql= "select * from (select * from treatise order by state,class,date desc) as T WHERE T.state>3 and T.name like '%"+name+"%' and T.treId LIMIT " +
				""+(pageNow-1)+" ,"+pageSize+"";
		int i=0;
		List<Treatise> treatises=new ArrayList<Treatise>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> list = mysqlcurd.SelectData(sql, null);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			treatises.add(new MapToBean().MapToTreatise(map));
			i++;
		}
		return treatises;
	}
	
	/**
	 * @category 分页查询所有论著
	 * @param pageSize int 一个页面要显示pageSize条个数据
	 * @param pageNow int 从pageNow当前的数据来显示
	 * @param date1 Date 开始日期
	 * @param date2 Date 结束日期
	 * @return Treatise List<Treatise> 返回查询到的论著列表
	 */
	public List<Treatise> getSegregateTreatiseByDate(Date date1,Date date2,int pageNow,int pageSize) {
		String sql= "select * from (select * from treatise order by state,class,date desc) as T WHERE T.state>3 and T.date>='"+date1+"' and T.date<='"+date2+"' and T.treId LIMIT " +
				""+(pageNow-1)+" ,"+pageSize+"";
		int i=0;
		List<Treatise> treatises=new ArrayList<Treatise>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> list = mysqlcurd.SelectData(sql, null);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			treatises.add(new MapToBean().MapToTreatise(map));
			i++;
		}
		return treatises;
	}
	
	
	/**
	 * @category 根据名称统计论著的数量
	 * @param name String 论著的名称
	 * @return sum int 论著的数量
	 */
		public int getCountTreatiseByName(String name)
		{
			String sql=null;
			int count=0;
			if(name.equals("")||name==null)
			{
				sql="select count(*) from treatise where state>3";
			}
			else
			{
				sql="select count(*) from treatise where state>3 and name like '%"+name+"%'";
			}

			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql, null);
			Map map=lists.get(0);
			count=Integer.parseInt(map.get(0).toString());
			return count;
		}
		
		
		/**
		 * @category 根据日期统计论著的数量
		 * @param date1 Date 开始日期
		 * @param date2 Date 结束日期
		 * @return count int 论著的数量
		 */
		public int getCountTreatiseByDate(Date date1,Date date2)
		{
			int count=0;
			String sql="select count(*) from treatise where state>3 and date>='"+date1+"' and date<='"+date2+"'";
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql, null);
			Map map=lists.get(0);
			count=Integer.parseInt(map.get(0).toString());
			return count;
		}
		
		
		/**
		 * @author yaojun
		 * @category 按照审核状态分页查询所有的论著
		 * @param state 4表示未审核，5表示已经审核
		 * @return Lsit
		 */
		public List<Treatise> getSegregateTreatiseByState(int state,int pageNow,int pageSize) {
			String sql= "select * from treatise WHERE state=? and treId LIMIT " +
				""+(pageNow-1)+" ,"+pageSize+"";
			int i=0;
			List<Treatise> treatises=new ArrayList<Treatise>();
			MysqlCrud mysqlcurd=new MysqlCrud();
			String[]  parameter={String.valueOf(state)};
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			while(i<list.size()){
				Map map=list.get(i);
				treatises.add(new MapToBean().MapToTreatise(map));
				i++;
			}
			return treatises;
		}
		
		
		/**
		 * @category 根据审核状态统计论著的数量
		 * @return count int 论著的数量
		 */
		public int getCountTreatiseByState(int state)
		{
			int count=0;
			String sql="select count(*) from treatise where state=?";
			MysqlCrud mysqlcurd=new MysqlCrud();
			String[]  parameter={String.valueOf(state)};
			List<Map> lists=mysqlcurd.SelectData(sql, parameter);
			Map map=lists.get(0);
			count=Integer.parseInt(map.get(0).toString());
			return count;
		}
		
		
		/**
		 * @author yaojun
		 * @category 按照不同的条件来查询所有的论著并进行分页,针对特定的方向
		 * @return Treatise List<Treatise> 返回查询到的论著列表
		 */
		public List<Treatise> getSegregateTreatiseByDirection(Treatise treatise,String date2,int direction,int pageNow,int pageSize,int state,List<String> orders) {
			int j=0;
			String sql=null;
			
			if(orders.size()>0)
			{
				String sqlOrder="";
				for(int i=0;i<orders.size();i++)
				{
					if(i!=orders.size()-1)
					sqlOrder+=orders.get(i)+",";
					else
					sqlOrder+=orders.get(i);
				
				}
				sql="select * from (select * from treatise order by "+sqlOrder+") as tr ,teacher as T where tr.teaId = T.teaId and T.direction=? and tr.state>'"+state+"'";
			}
			else
			sql="select * from treatise as Tr,teacher as T where Tr.teaId=T.teaId and T.direction=? and state>'"+state+"' ";
			
			String sql2=" and treId LIMIT "+(pageNow-1)+" ,"+pageSize+" ";
			List<Treatise> treatises=new ArrayList<Treatise>();
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			
			paras.add(String.valueOf(direction));
			if(treatise!=null)
			{
				if(treatise.getName()!=null)
				{
					sql1.add(" Tr.name like ? ");
					paras.add("%"+String.valueOf(treatise.getName())+"%");
				}
				
				if(treatise.getDate()!=null)
				{
					sql1.add(" Tr.date>=? and Tr.date<=? ");
					paras.add(String.valueOf(treatise.getDate()));
					paras.add(date2);
				}
				if(treatise.getAuthor()!=null)
				{
					sql1.add(" Tr.author like ? ");
					paras.add("%"+String.valueOf(treatise.getAuthor())+"%");
				}
				if(treatise.getState()!=null)
				{
					sql1.add(" Tr.state=? ");
					paras.add(String.valueOf(treatise.getState()));
				}
				if(treatise.getClass_()!=null)
				{
					sql1.add(" Tr.class=? ");
					paras.add(String.valueOf(treatise.getClass_()));
				}
				if(treatise.getPress()!=null)
				{
					sql1.add(" Tr.press=? ");
					paras.add(String.valueOf(treatise.getPress()));
				}
				if(treatise.getBookNum()!=null)
				{
					sql1.add(" Tr.bookNum=? ");
					paras.add(String.valueOf(treatise.getBookNum()));
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
					sql+="and"+sql1.get(i);
			}
			
			//加入最后一个,该字符是用于分页
			sql+=sql2;
			System.out.println("sql222为："+sql);
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			//System.out.println(list.size());
			while(j<list.size()){
				Map map=list.get(j);
				treatises.add(new MapToBean().MapToTreatise(map));
				j++;
			}
			return treatises;
		}
		
		
		/**
		 * @author yaojun
		 * @category 按照不同的条件来查询所有的论著,针对特定的方向
		 * @return Treatise List<Treatise> 返回查询到的论著列表
		 */
		public List<Treatise> getTreatiseByDirection(Treatise treatise,String date2,int direction,int state) {
//			String sql= "select * from treatise WHERE date>='"+date1+"' and date<='"+date2+"' and treId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			int j=0;
			String sql="select * from treatise as Tr,teacher as T where Tr.teaId=T.teaId and T.direction=? and state>'"+state+"' ";
			List<Treatise> treatises=new ArrayList<Treatise>();
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			
			paras.add(String.valueOf(direction));
			if(treatise!=null)
			{
				if(treatise.getName()!=null)
				{
					sql1.add(" Tr.name like ? ");
					paras.add("%"+String.valueOf(treatise.getName())+"%");
				}
				
				if(treatise.getDate()!=null)
				{
					sql1.add(" Tr.date>=? and Tr.date<=? ");
					paras.add(String.valueOf(treatise.getDate()));
					paras.add(date2);
				}
				if(treatise.getAuthor()!=null)
				{
					sql1.add(" Tr.author like ? ");
					paras.add("%"+String.valueOf(treatise.getAuthor())+"%");
				}
				if(treatise.getState()!=null)
				{
					sql1.add(" Tr.state=? ");
					paras.add(String.valueOf(treatise.getState()));
				}
				if(treatise.getClass_()!=null)
				{
					sql1.add(" Tr.class=? ");
					paras.add(String.valueOf(treatise.getClass_()));
				}
				if(treatise.getPress()!=null)
				{
					sql1.add(" Tr.press=? ");
					paras.add(String.valueOf(treatise.getPress()));
				}
				if(treatise.getBookNum()!=null)
				{
					sql1.add(" Tr.bookNum=? ");
					paras.add(String.valueOf(treatise.getBookNum()));
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
				sql+="and"+sql1.get(i);
			}
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			//System.out.println(list.size());
			while(j<list.size()){
				Map map=list.get(j);
				treatises.add(new MapToBean().MapToTreatise(map));
				j++;
			}
			return treatises;
		}
		
		
		/**
		 * @author yaojun
		 * @category 按照不同的条件来查询所有的论著
		 * @return Treatise List<Treatise> 返回查询到的论著列表
		 */
		public List<Treatise> getTreatiseByAll(Treatise treatise,String date2,int direction,int state) {
//			String sql= "select * from treatise WHERE date>='"+date1+"' and date<='"+date2+"' and treId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			int j=0;
			String sql="select * from treatise as Tr,teacher as T where Tr.teaId=T.teaId and T.direction=? and state>'"+state+"' ";
			List<Treatise> treatises=new ArrayList<Treatise>();
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			
			paras.add(String.valueOf(direction));
			
			if(treatise!=null)
			{
				if(treatise.getName()!=null)
				{
					sql1.add(" Tr.name like ? ");
					paras.add("%"+String.valueOf(treatise.getName())+"%");
				}
				
				if(treatise.getDate()!=null)
				{
					sql1.add(" Tr.date>=? and Tr.date<=? ");
					paras.add(String.valueOf(treatise.getDate()));
					paras.add(date2);
				}
				if(treatise.getAuthor()!=null)
				{
					sql1.add(" Tr.author like ? ");
					paras.add("%"+String.valueOf(treatise.getAuthor())+"%");
				}
				if(treatise.getState()!=null)
				{
					sql1.add(" Tr.state=? ");
					paras.add(String.valueOf(treatise.getState()));
				}
				if(treatise.getClass_()!=null)
				{
					sql1.add(" Tr.class=? ");
					paras.add(String.valueOf(treatise.getClass_()));
				}
				if(treatise.getPress()!=null)
				{
					sql1.add(" Tr.press=? ");
					paras.add(String.valueOf(treatise.getPress()));
				}
				if(treatise.getBookNum()!=null)
				{
					sql1.add(" Tr.bookNum=? ");
					paras.add(String.valueOf(treatise.getBookNum()));
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
				sql+="and"+sql1.get(i);
			}
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			//System.out.println(list.size());
			while(j<list.size()){
				Map map=list.get(j);
				treatises.add(new MapToBean().MapToTreatise(map));
				j++;
			}
			return treatises;
		}
		
		
		/**
		 * @author yaojun
		 * @category 按照不同的条件来查询所有的论著的数量,针对特定的方向
		 * @return int
		 */
		public int getTreatiseCountByDirection(Treatise treatise,String date2,int direction,int state) {
//			String sql= "select * from treatise WHERE date>='"+date1+"' and date<='"+date2+"' and treId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			int count=0;
			String sql="select count(*) from treatise as Tr,teacher as T where Tr.teaId=T.teaId and T.direction=? and state>'"+state+"' ";
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			
			paras.add(String.valueOf(direction));
			if(treatise!=null)
			{
				if(treatise.getName()!=null)
				{
					sql1.add(" Tr.name like ? ");
					paras.add("%"+String.valueOf(treatise.getName())+"%");
				}
				
				if(treatise.getDate()!=null)
				{
					sql1.add(" Tr.date>=? and Tr.date<=? ");
					paras.add(String.valueOf(treatise.getDate()));
					paras.add(date2);
				}
				if(treatise.getAuthor()!=null)
				{
					sql1.add(" Tr.author like ? ");
					paras.add("%"+String.valueOf(treatise.getAuthor())+"%");
				}
				if(treatise.getState()!=null)
				{
					sql1.add(" Tr.state=? ");
					paras.add(String.valueOf(treatise.getState()));
				}
				if(treatise.getClass_()!=null)
				{
					sql1.add(" Tr.class=? ");
					paras.add(String.valueOf(treatise.getClass_()));
				}
				if(treatise.getPress()!=null)
				{
					sql1.add(" Tr.press=? ");
					paras.add(String.valueOf(treatise.getPress()));
				}
				if(treatise.getBookNum()!=null)
				{
					sql1.add(" Tr.bookNum=? ");
					paras.add(String.valueOf(treatise.getBookNum()));
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
				sql+="and"+sql1.get(i);
			}
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql, parameter);
			Map map=lists.get(0);
			count=Integer.parseInt(map.get(0).toString());
			return count;
		}
		
		
		/**
		 * @author yaojun
		 * @category 按照不同的条件来查询所有的论著,针对特定的教师工号
		 * @return Treatise List<Treatise> 返回查询到的论著列表
		 */
		public List<Treatise> getTreatiseByUser(Treatise treatise,String date2,Teacher teacher) {
//			String sql= "select * from treatise WHERE date>='"+date1+"' and date<='"+date2+"' and treId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			int j=0;
			String sql="select * from treatise as Tr,teacher as T where Tr.teaId=T.teaId and T.user=? ";
			List<Treatise> treatises=new ArrayList<Treatise>();
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			
			paras.add(teacher.getUser());
			if(treatise!=null)
			{
				if(treatise.getName()!=null)
				{
					sql1.add(" Tr.name like ? ");
					paras.add("%"+String.valueOf(treatise.getName())+"%");
				}
				
				if(treatise.getDate()!=null)
				{
					sql1.add(" Tr.date>=? and Tr.date<=? ");
					paras.add(String.valueOf(treatise.getDate()));
					paras.add(date2);
				}
				if(treatise.getAuthor()!=null)
				{
					sql1.add(" Tr.author like ? ");
					paras.add("%"+String.valueOf(treatise.getAuthor())+"%");
				}
				if(treatise.getClass_()!=null)
				{
					sql1.add(" Tr.class=? ");
					paras.add(String.valueOf(treatise.getClass_()));
				}
				if(treatise.getPress()!=null)
				{
					sql1.add(" Tr.press=? ");
					paras.add(String.valueOf(treatise.getPress()));
				}
				if(treatise.getBookNum()!=null)
				{
					sql1.add(" Tr.bookNum=? ");
					paras.add(String.valueOf(treatise.getBookNum()));
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
				sql+="and"+sql1.get(i);
			}
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			//System.out.println(list.size());
			while(j<list.size()){
				Map map=list.get(j);
				treatises.add(new MapToBean().MapToTreatise(map));
				j++;
			}
			return treatises;
		}
	
}
